﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data;
using System.Globalization;
using System.Collections;
using System.Collections.ObjectModel;
using System.Data.SqlClient;
using MockPrjTeam4.Entity;
using MockPrjTeam4.Common;

namespace MockPrjTeam4.DAO
{
    public class OrganisationDAO
    {
        /// <summary>
        /// OrganisationObject
        /// </summary>
        /// <param name="read"></param>
        /// <returns></returns>
        public static Organisation OrganisationObject(IDataReader read)
        {
            
            return new Organisation
            {
                OrganisationId = Methods.ConvertNumber(read[Organisation.FieldOrganistionId].ToString()),
                OrganisationName = Methods.ConvertString(read[Organisation.FieldOrganistionName].ToString()),
                ShortDescription = Methods.ConvertString(read[Organisation.FieldShortDescription].ToString()),
                AddressId = Methods.ConvertNumber(read[Organisation.FieldAddressId].ToString()),
                BusinessId = Methods.ConvertNumber(read[Organisation.FieldBusinessId].ToString()),
                PhoneNumber = Methods.ConvertString(read[Organisation.FieldPhoneNumber].ToString()),
                TownId = Methods.ConvertNumber(read[Organisation.FieldTownId].ToString()),
                CountyId = Methods.ConvertNumber(read[Organisation.FieldCountyId].ToString()),
                Countryid = Methods.ConvertNumber(read[Organisation.FieldCountryId].ToString()),
                PreferredOrganisation = Methods.ConvertBoolean(read[Organisation.FieldPreferredOrganisation].ToString()),
                FullDescription = Methods.ConvertString(read[Organisation.FieldFullDescription].ToString()),
                ContactId = Methods.ConvertNumber(read[Organisation.FieldContactId].ToString()),
                Fax = Methods.ConvertString(read[Organisation.FieldFax].ToString()),
                Email = Methods.ConvertString(read[Organisation.FieldEmail].ToString()),
                WebAddress = Methods.ConvertString(read[Organisation.FieldWebAddress].ToString()),
                CharityNumber = Methods.ConvertString(read[Organisation.FieldCharityNumber].ToString()),
                CompanyNumber = Methods.ConvertString(read[Organisation.FieldCompanyNumber].ToString()),
                OrganisationSpecialism = Methods.ConvertString(read[Organisation.FieldOrganisationSpecialism].ToString()),
                ServiceDisabilities = Methods.ConvertString(read[Organisation.FieldServiceDisabilities].ToString()),
                ServiceBarriers = Methods.ConvertString(read[Organisation.FieldServiceBarriers].ToString()),
                ServiceBenefits = Methods.ConvertString(read[Organisation.FieldServiceBenefits].ToString()),
                ServiceCircumstances = Methods.ConvertString(read[Organisation.FieldServiceCircumstances].ToString()),
                ServiceEthnicity = Methods.ConvertString(read[Organisation.FieldServiceEthnicity].ToString()),
                Accreditation = Methods.ConvertString(read[Organisation.FieldAccreditation].ToString()),
                IsActive = Methods.ConvertBoolean(read[Organisation.FieldIsActive].ToString())
            };
        }

        public static IList<Organisation> GetListOrganisations()
        {
            IList<Organisation> org = new List<Organisation>();
            string query = string.Format("SELECT * FROM Organisations");
            IDataReader read = SqlHelper.ExecuteReader(query);
            while (read.Read())
            {
                org.Add(OrganisationObject(read));
            }
            return org;
        }

        public static IList<Organisation> GetListActiveOrganisations()
        {
            IList<Organisation> org = new List<Organisation>();
            string query = string.Format("SELECT * FROM Organisations WHERE IsActive='true'");
            IDataReader read = SqlHelper.ExecuteReader(query);
            while (read.Read())
            {
                org.Add(OrganisationObject(read));
            }
            return org;
        }

        public static bool AddOrganisation(Organisation org)
        {
            string query = string.Format(CultureInfo.CurrentCulture,
                "INSERT INTO Organisations(OrganisationName, ShortDescription, AddressId, BusinessId, " +
                "PhoneNumber, TownId, CountyId, Countryid, PreferredOrganisation, FullDescription, ContactId, " +
                "Fax, Email, WebAddress, CharityNumber, CompanyNumber, OrganisationSpecialism, " +
                "ServiceDisabilities, ServiceBarriers, ServiceBenefits, ServiceCircumstances, " +
                "ServiceEthnicity, Accreditation, IsActive) " +
                "VALUES(@OrgName, @ShortDes, @AddId, @BusinessId, @PhoneNum, @TownId, @CountyId, @CountryId, " +
                "@PreOrg, @FullDes, @ContactId, @Fax, @Email, @WebAdd, @CharityNum, @CompanyNum, " +
                "@OrganisationSpecialism, @ServiceDisabilities, @ServiceBarriers, @ServiceBenefits, " +
                "@ServiceCircumstances, @ServiceEthnicity, @Accreditation, " +
                "@IsActive)");
            var parameter = new Collection<SqlParameter>
            {
                new SqlParameter("@OrgName", org.OrganisationName),
                new SqlParameter("@ShortDes", org.ShortDescription),
                new SqlParameter("@AddId", org.AddressId),
                new SqlParameter("@BusinessId", org.BusinessId),
                new SqlParameter("@PhoneNum", org.PhoneNumber),
                new SqlParameter("@TownId", org.TownId),
                new SqlParameter("@CountyId", org.CountyId),
                new SqlParameter("@CountryId", org.Countryid),
                new SqlParameter("@PreOrg", org.PreferredOrganisation),
                new SqlParameter("@FullDes", org.FullDescription),
                new SqlParameter("@Fax", org.Fax),
                new SqlParameter("@Email", org.Email),
                new SqlParameter("@WebAdd", org.WebAddress),
                new SqlParameter("@CharityNum", org.CharityNumber),
                new SqlParameter("@CompanyNum", org.CompanyNumber),
                new SqlParameter("@OrganisationSpecialism", org.OrganisationSpecialism),
                new SqlParameter("@ServiceDisabilities", org.ServiceDisabilities),
                new SqlParameter("@ServiceBarriers", org.ServiceBarriers),
                new SqlParameter("@ServiceBenefits", org.ServiceBenefits),
                new SqlParameter("@ServiceCircumstances", org.ServiceCircumstances),
                new SqlParameter("@ServiceEthnicity", org.ServiceEthnicity),
                new SqlParameter("@Accreditation", org.Accreditation),
                new SqlParameter("@IsActive", org.IsActive)
            };

            if (org.ContactId != -1)
                parameter.Add(new SqlParameter("@ContactId", org.ContactId));
            else
                parameter.Add(new SqlParameter("@ContactId", DBNull.Value));

            SqlHelper.ExecuteNonQuery(query, parameter);
            return true;
        }

        public static bool EditOrganisation(Organisation org)
        {
            string query = string.Format(CultureInfo.CurrentCulture,
                "UPDATE Organisations SET OrganisationName = @OrgName, ShortDescription = @ShortDes, " +
                "AddressId = @AddId, BusinessId = @BusinessId, " +
                "PhoneNumber = @PhoneNum, TownId = @TownId, CountyId = @CountyId, " +
                "Countryid = @CountryId, PreferredOrganisation = @PreOrg, FullDescription = @FullDes, " +
                "ContactId = @ContactId, Fax = @Fax, Email = @Email, WebAddress = @WebAdd, " +
                "CharityNumber = @CharityNum, CompanyNumber = @CompanyNum, IsActive = @IsActive, " +
                "OrganisationSpecialism = @OrganisationSpecialism, " +
                "ServiceDisabilities = @ServiceDisabilities, " +
                "ServiceBarriers = @ServiceBarriers, " +
                "ServiceBenefits = @ServiceBenefits, " +
                "ServiceCircumstances = @ServiceCircumstances, " +
                "ServiceEthnicity = @ServiceEthnicity, " +
                "Accreditation = @Accreditation " +
                "WHERE OrganisationId=@OrgId"
                );
            var parameter = new Collection<SqlParameter>
            {
                new SqlParameter("@OrgId", org.OrganisationId),
                new SqlParameter("@OrgName", org.OrganisationName),
                new SqlParameter("@ShortDes", org.ShortDescription),
                new SqlParameter("@AddId", org.AddressId),
                new SqlParameter("@BusinessId", org.BusinessId),
                new SqlParameter("@PhoneNum", org.PhoneNumber),
                new SqlParameter("@TownId", org.TownId),
                new SqlParameter("@CountyId", org.CountyId),
                new SqlParameter("@CountryId", org.Countryid),
                new SqlParameter("@PreOrg", org.PreferredOrganisation),
                new SqlParameter("@FullDes", org.FullDescription),
                new SqlParameter("@Fax", org.Fax),
                new SqlParameter("@Email", org.Email),
                new SqlParameter("@WebAdd", org.WebAddress),
                new SqlParameter("@CharityNum", org.CharityNumber),
                new SqlParameter("@CompanyNum", org.CompanyNumber),
                new SqlParameter("@OrganisationSpecialism", org.OrganisationSpecialism),
                new SqlParameter("@ServiceDisabilities", org.ServiceDisabilities),
                new SqlParameter("@ServiceBarriers", org.ServiceBarriers),
                new SqlParameter("@ServiceBenefits", org.ServiceBenefits),
                new SqlParameter("@ServiceCircumstances", org.ServiceCircumstances),
                new SqlParameter("@ServiceEthnicity", org.ServiceEthnicity),
                new SqlParameter("@Accreditation", org.Accreditation),
                new SqlParameter("@IsActive", org.IsActive)
            };
            if (org.ContactId == -1) parameter.Add(new SqlParameter("@ContactId", DBNull.Value));
            else parameter.Add(new SqlParameter("@ContactId", org.ContactId));
            
            SqlHelper.ExecuteNonQuery(query, parameter);
            return true;
        }

        public static DataTable Search(string range, bool inActive)
        {
            string query = string.Format("SELECT OrganisationId, OrganisationName, AddressLine1 AS AddressLine1, " +
                                            "PostCode, FirstName + ' ' + Surname AS ContactName, Organisations.IsActive " +
                                            "AS IsActive " +
                                            "FROM Addresses, Contacts, Organisations " +
                                            "WHERE Addresses.AddressId = Organisations.AddressId " +
                                            "AND Contacts.ContactId = Organisations.ContactId " +
                                            "AND OrganisationName LIKE @OrgName ");
            var parameter = new Collection<SqlParameter>
            {
                new SqlParameter("@orgName", range)
            };
            if (inActive == true)
            {
                return SqlHelper.ExecuteQuery(query, parameter);
            }
            else
            {
                query += string.Format(" AND Organisations.IsActive='true'");
                return SqlHelper.ExecuteQuery(query, parameter);
            }
        }

        public static bool Delete(int id)
        {
            string query = string.Format("DELETE FROM Organisations WHERE OrganisationId=@OrgId");
            var parameter = new Collection<SqlParameter>
            {
                new SqlParameter("@OrgId", id)
            };
            SqlHelper.ExecuteNonQuery(query, parameter);
            return true;
        }

        public static DataTable GetTableOrganisations(bool inActive)
        {
            string query = string.Format("SELECT OrganisationId, OrganisationName, AddressLine1 AS AddressLine1, " +
                                            "PostCode, FirstName + ' ' + Surname AS ContactName, Organisations.IsActive " +
                                            "AS IsActive " +
                                            "FROM Organisations " +
                                            "LEFT JOIN Addresses ON Addresses.AddressId = Organisations.AddressId " +
                                            "LEFT JOIN Contacts ON Contacts.ContactId = Organisations.ContactId ");
            if (inActive == true)
            {
                return SqlHelper.ExecuteQuery(query + " ORDER BY OrganisationName");
            }
            else
            {
                query += string.Format(" WHERE Organisations.IsActive='true' ORDER BY OrganisationName");
                return SqlHelper.ExecuteQuery(query);
            }
        }

        public static Organisation SearchOrgName(string orgName)
        {
            string query = string.Format("SELECT * FROM Organisations WHERE OrganisationName=@OrgName");
            var parameter = new Collection<SqlParameter>
            {
                new SqlParameter("@OrgName", orgName)
            };
            IDataReader read = SqlHelper.ExecuteReader(query, parameter);
            if (read.Read())
            {
                return OrganisationObject(read);
            }
            else
            {
                return null;
            }
        }

        public static Organisation Search(int orgName)
        {
            string query = string.Format("SELECT * FROM Organisations WHERE OrganisationId=@OrgName");
            var parameter = new Collection<SqlParameter>
            {
                new SqlParameter("@OrgName", orgName)
            };
            IDataReader read = SqlHelper.ExecuteReader(query, parameter);
            if (read.Read())
            {
                return OrganisationObject(read);
            }
            else
            {
                return null;
            }
        }

        public static DataTable GetOrganisationsSP()
        {
            int currentPageNumber = 1;
            const int PAGE_SIZE = 15;
            var parameter = new Collection<SqlParameter>
            {
                new SqlParameter("@startRowIndex", currentPageNumber),
                new SqlParameter("@maximumRows", PAGE_SIZE),
                new SqlParameter("@totalRows", ParameterDirection.Output)
            };
            return SqlHelper.ExecuteDataAdapter("usp_GetOrganisations", parameter);
        }

        public static bool SetOrgActive(int orgId)
        {
            string query = string.Format("UPDATE Organisations SET IsActive='true' WHERE OrganisationId=@OrgId ");
            var parameter = new Collection<SqlParameter>
            {
                new SqlParameter("@OrgId", orgId)
            };
            try
            {
                SqlHelper.ExecuteQuery(query, parameter);
                return true;
            }
            catch (System.Exception ex)
            {
                return false;
            }
        }

        public static DataTable SearchTableOrg(int orgId)
        {
            string query = string.Format("SELECT Organisations.AddressId AS AddressId, AddressLine1, AddressLine2, " +
                "AddressLine3, PostCode, " +
                "TownName, CountyName, Organisations.BusinessId AS BusinessId, BusinessName, SICCode, FirstName + ' ' + Surname AS ContactName, " +
                "CountryName, Addresses.TownId AS TownId, Towns.CountyId AS CountyId, Countries.CountryId AS CountryId " +
                "FROM Organisations " +
                "LEFT JOIN Addresses ON Organisations.AddressId = Addresses.AddressId " +
                "LEFT JOIN Towns ON Addresses.TownId = Towns.TownId " +
                "LEFT JOIN Counties ON Towns.CountyId = Counties.CountyId " +
                "LEFT JOIN Countries ON Counties.CountryId = Countries.CountryId " +
                "LEFT JOIN Contacts ON Organisations.ContactId = Contacts.ContactId " +
                "LEFT JOIN BusinessTypes ON Organisations.BusinessId = BusinessTypes.BusinessId " +
                "WHERE OrganisationId=@OrgId");
            var parameter = new Collection<SqlParameter>
            {
                new SqlParameter("@OrgId", orgId)
            };
            try
            {
                return SqlHelper.ExecuteQuery(query, parameter);
            }
            catch (System.Exception ex)
            {
                return null;
            }
        }
    }
}
